package jsyf.oa.dao.system;

import com.alibaba.dubbo.common.utils.StringUtils;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import jsyf.com.dao.BaseJdbcDao;
import jsyf.oa.entity.system.CompanyEntity;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;

/**
 * @author: 徐波
 * @creat: 2017/11/23 0023 9:59
 * description:
 * 公司的DAO层
 */
@Repository
public class CompanyDao extends BaseDao {

    private static Integer token = 1;

    public CompanyDao() {
        this.iBaseEntity = new CompanyEntity();
    }

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, CompanyEntity.class);
    }

    public List<JSONObject> getPartCompanyId(String userId,String companyId){
        StringBuffer sql=new StringBuffer();
        sql.append(" SELECT\n" +
                "\tt_sys_company.ID as id,\n" +
                "  t_sys_company.`NAME` as name,\n" +
                "  t_sys_company.PARENT_ID as parentId\n" +
                "FROM\n" +
                "\tt_sys_company\n" +
                "LEFT JOIN t_sys_depart ON t_sys_company.ID = t_sys_depart.COMPANY_ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "LEFT JOIN t_sys_user_2_role ON t_sys_user_2_role.ROLE_ID = t_sys_role.ID\n" +
                "WHERE\n" +
                "\tt_sys_user_2_role.USER_ID = '"+userId+"'\n" +
                "   AND t_sys_company.ID !='"+companyId+"'\n" +
                "GROUP BY t_sys_company.ID");
        return super.queryForJsonList(sql.toString());
    }

    public List<CompanyEntity> getByPage(String condition, Object[] params, int requestPage, int pageSize) {
        List<CompanyEntity> list = new ArrayList<>();
        List<IBaseEntity> iBaseEntityList = super.getIBaseEntityListByPage(condition, params, requestPage, pageSize);
        for (int i = 0; i < iBaseEntityList.size(); i++) {
            list.add((CompanyEntity) iBaseEntityList.get(i));
        }
        return list;
    }

    public Integer add(IBaseEntity entity) {
        entity.setId(super.generateKey());
        entity.setCreateTime(super.getCurrentTime());
        entity.setUpdateTime(super.getCurrentTime());
        return super.insert(entity.getTableName(), entity.getJSONObject());
    }

    public List<CompanyEntity> getByCondition(String condition, Object[] params) {
        return getByPage(condition, params, 1, Integer.MAX_VALUE);
    }

    public CompanyEntity getById(String id) {
        return (CompanyEntity) super.getIBaseEntityById(id);
    }

    public void getChildrenIds(String companyId, List<String> listCompanyIds) {
        CompanyEntity companyEntity = this.getById(companyId);
        List<JSONObject> children = this.getJSONListByCondition(" AND DEL_FLAG=0 AND PARENT_ID = ? ", new Object[]{companyEntity.getId()});
        if (children == null || children.size() == 0) {

        } else {
            for (int i = 0; i < children.size(); i++) {
                String childrenCompanyId = children.get(i).getString("id");
                listCompanyIds.add(childrenCompanyId);
                getChildrenIds(childrenCompanyId, listCompanyIds);
            }
        }
    }

    public void getParentIds(String companyId, List<String> listCompanyIds) {
        CompanyEntity companyEntity = this.getById(companyId);
        if (companyEntity == null || ("0").equals(companyEntity.getParentId()) || StringUtils.isEmpty(companyEntity.getParentId())) {

        } else {
            listCompanyIds.add(companyEntity.getParentId());
            getParentIds(companyEntity.getParentId(), listCompanyIds);
        }
    }

    public Boolean sort(String companyIds) {
        List<String> list = BaseJdbcDao.stringToList(companyIds);
        for (int i = 0; i < list.size(); i++) {
            JSONObject obj = new JSONObject();
            obj.put("ID", list.get(i));
            obj.put("SORT_NO", i);

            JSONObject objId = new JSONObject();
            objId.put("ID", list.get(i));
            super.update(iBaseEntity.getTableName(), obj, objId);
        }
        return true;
    }


    public List<JSONObject> getCompanyByManage(String target, Integer type, String dateTime, String yesterday) {
        int len = dateTime.length();
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT obj.* ,");
        sb.append(" CASE \n");
        sb.append(" WHEN @rowtotal = obj.averageComplete THEN \n");
        sb.append("  @rownum \n");
        sb.append(" WHEN @rowtotal := obj.averageComplete THEN \n");
        sb.append(" @rownum :=@rownum + 1 \n");
        sb.append(" WHEN @rowtotal = 0 THEN \n");
        sb.append(" @rownum :=@rownum + 1 \n ");
        sb.append(" END AS rownum \n ");
        sb.append(" FROM( \n ");
        sb.append(" SELECT  \n ");
        sb.append(" v_sys_company.companyId, \n");
        sb.append(" v_sys_company.companyName,  \n ");
        sb.append(" IFNULL(v_sys_company.managerName,'') as managerName, \n ");
        sb.append(" IFNULL(v_sys_company.salerManagerName,'') as salerManagerName,  \n ");
        sb.append(" v_sys_company.companySortNo,  \n ");
        sb.append(" IFNULL(b_sys_target.target,0) as target,  \n ");
        sb.append(" SUM(manager.targetNumber) AS standard,  \n ");
        sb.append(" IFNULL(v_sys_leave.leaveNumber,0) as leaveNumber, \n ");
        sb.append(" IFNULL(t_sys_complete.complete,0) as complete,  \n ");
        sb.append(" (select SUM(roleName = '区域经理') FROM v_sys_user WHERE v_sys_user.companyId =   \n ");
        sb.append(" v_sys_company.companyId AND v_sys_user.delFlag=0) as regionManagerNum,  \n ");
        if (len == 10) {
            sb.append(" (select IFNULL(TRUNCATE(complete/(regionManagerNum-IFNULL(leaveNumber,0)),2),0.00)) as  \n ");

        } else {
            sb.append(" (select IFNULL(TRUNCATE(complete/regionManagerNum,2),0.00)) as  \n ");
        }
        sb.append(" averageComplete,  \n ");
        sb.append(" (select IFNULL(TRUNCATE(complete/target*100,2),0.00)) as  completionRate \n ");
        sb.append(" FROM v_sys_company   \n ");
        sb.append(" LEFT JOIN (  \n ");
        sb.append(" select COMPANY_ID,SUM(" + target + ") as target from t_sys_target WHERE  \n ");
        if (len == 10) {
            if (token.equals(type)) {
                sb.append(" TARGET_DATE = " + "'" + dateTime + "'\n");
            } else {
                sb.append(" TARGET_DATE = " + "'" + dateTime + "' \n");
            }
        } else {
            sb.append(" LEFT(TARGET_DATE,7)= " + "'" + dateTime + "' \n");
        }
        sb.append(" GROUP BY COMPANY_ID \n ");
        sb.append(" ) as b_sys_target on v_sys_company.companyId = b_sys_target.COMPANY_ID \n");
        sb.append(" LEFT JOIN ( \n");
        sb.append(" SELECT COMPANY_ID,SUM(VISIT_MODE = " + type + ") as complete from t_wrt_customer_visit \n");
        if (len == 10) {
            if (token.equals(type)) {
                sb.append(" WHERE REPORT_DATE = " + "'" + dateTime + "'" + " \n");

            } else {
                sb.append(" WHERE REPORT_DATE = " + "'" + dateTime + "'" + " \n");
            }
        } else {
            sb.append(" WHERE LEFT(REPORT_DATE,7) = " + "'" + dateTime + "'" + "  \n");
        }
        sb.append(" GROUP BY COMPANY_ID \n");
        sb.append(" ) as t_sys_complete on v_sys_company.companyId = t_sys_complete.COMPANY_ID  \n");
        sb.append(" LEFT JOIN (");
        sb.append(" select companyId,targetNumber from v_sys_user WHERE roleName='区域经理' AND delFlag=0 GROUP BY roleId");
        sb.append(" ) AS manager ON manager.companyId=v_sys_company.companyId");
        sb.append(" LEFT JOIN (");
        sb.append(" SELECT COUNT(1) as leaveNumber,v_sys_user.companyId FROM t_sys_leave ");
        sb.append(" LEFT JOIN v_sys_user ON v_sys_user.id=t_sys_leave.USER_ID ");
        if (len == 10) {
            if (token.equals(type)) {
                sb.append(" WHERE LEAVE_DATE = " + "'" + dateTime + "'" + " \n");

            } else {
                sb.append(" WHERE LEAVE_DATE = " + "'" + dateTime + "'" + " \n");
            }
        } else {
            sb.append(" WHERE LEFT(LEAVE_DATE,7) = " + "'" + dateTime + "'" + "  \n");
        }
        sb.append(" AND roleName='区域经理'");
        sb.append(" GROUP BY v_sys_user.companyId");
        sb.append(" ) AS v_sys_leave ON v_sys_leave.companyId=v_sys_company.companyId");
        sb.append(" where companyParentId = '201712161613432EAF27DBCCB7D0E6CA' \n");
        sb.append(" and companyName NOT like '%大客户%' and companyDelFlag = 0\n");
        sb.append(" GROUP BY v_sys_company.companyId \n");
        sb.append(" ORDER BY averageComplete DESC,v_sys_company.companySortNo) as obj, \n");
        sb.append(" (SELECT @rownum := 0 ,@rowtotal := NULL) r \n");

        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public List<JSONObject> getCompanyByChart(String dateTime, String yesterday) {
        int len = dateTime.length();
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT t_sys_company.`NAME` as companyName,\n ");
        sb.append(" (select SUM(roleName = '区域经理') FROM v_sys_user WHERE v_sys_user.companyId =  \n ");
        sb.append(" t_sys_company.ID AND v_sys_user.delFlag=0) as regionManagerNum, \n ");
        if (len == 10) {
            sb.append("(select IFNULL(TRUNCATE(sum(t_wrt_customer_visit.REPORT_DATE=" + "'" + dateTime + "'\n");
        } else {
            sb.append("(select IFNULL(TRUNCATE(sum((LEFT(t_wrt_customer_visit.REPORT_DATE,7) =" + "'" + dateTime + "'" + ")" + "\n ");
        }
        sb.append(" and t_wrt_customer_visit.VISIT_MODE =0)/regionManagerNum,2),'0.00'))\n");
        sb.append(" as averageTelComplete, \n");
        if (len == 10) {
            sb.append("(select IFNULL(TRUNCATE(sum(t_wrt_customer_visit.REPORT_DATE=" + "'" + dateTime + "'" + " \n");
        } else {
            sb.append("(select IFNULL(TRUNCATE(sum((LEFT(t_wrt_customer_visit.REPORT_DATE,7) =" + "'" + dateTime + "'" + ")" + " \n");
        }
        sb.append(" and t_wrt_customer_visit.VISIT_MODE =1)/regionManagerNum,2),'0.00'))\n");
        sb.append(" as averageFaceComplete \n");
        sb.append(" from t_sys_company \n");
        sb.append(" LEFT JOIN t_wrt_customer_visit ON t_sys_company.ID = t_wrt_customer_visit.COMPANY_ID \n ");
        sb.append(" WHERE t_sys_company.PARENT_ID ='201712161613432EAF27DBCCB7D0E6CA'\n");
        sb.append(" AND t_sys_company.`NAME` NOT LIKE '%大客户%'\n ");
        sb.append(" AND t_sys_company.DEL_FLAG=0 \n");
        sb.append(" GROUP BY t_sys_company.SORT_NO ");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public JSONObject getCompanyParent(String id) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT company1.`NAME` AS name,company1.ID FROM `t_sys_company` AS company1 LEFT JOIN t_sys_company as company2 ON company1.ID=company2.PARENT_ID\n" +
                "WHERE company2.id='" + id + "'");
        return super.queryForJsonObject(sb.toString());

    }

    /**
     * mysql自定义函数递归查询下级公司(不包含自身)
     * 返回值为 -> ""(空字符串)、1,2,3(用逗号隔开的id)
     *
     * @return
     */
    public JSONObject getChilderCompanyByCompanyId(String company) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT getChilderCompanyByCompanyId('" + company + "') as companyIds");
        return super.queryForJsonObject(sb.toString());
        //自定义函数查询公司下级
        /*DROP FUNCTION IF EXISTS getChilderCompanyByCompanyId;
        CREATE FUNCTION getChilderCompanyByCompanyId(companyId VARCHAR(32))
        RETURNS text
        BEGIN
        DECLARE sTemp text;
        DECLARE sTempChd text;
        -- SET GLOBAL group_concat_max_len = 1844674407370955161;
        SET SESSION group_concat_max_len = 1844674407370955161;
        SET sTempChd = CAST(companyId AS CHAR);
        SET sTemp=NULL;
        WHILE sTempChd IS NOT NULL DO
        IF sTemp IS  NULL THEN  SET sTemp= sTempChd;
        ELSE SET sTemp= CONCAT(sTemp,',',sTempChd);
        END IF;
        SELECT GROUP_CONCAT(t_sys_company.ID) INTO sTempChd FROM t_sys_company WHERE FIND_IN_SET(t_sys_company.PARENT_ID,sTempChd)>0 AND t_sys_company.DEL_FLAG=0;
        END WHILE;
        IF locate(CONCAT(companyId,','),sTemp)>0
        THEN
        SET sTemp=REPLACE(sTemp,CONCAT(companyId,','),'');
        ELSE
        SET sTemp=REPLACE(sTemp,CAST(companyId AS CHAR),'');
        END IF;
        RETURN sTemp;
        END;*/
    }

}
